package com.Admin;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.Public.JDBConnection;
import com.User.DocItem;


public class DBconnect extends JDBConnection {
	
	public static int DBAdminRegister(AdminFile AF){//注册信息的数据库操作
		int row = 0;
		JDBConnection con = new JDBConnection();
		String sql="insert into yx_admin(adminName,TrueName,password,qq,longNum,shortNum,address," +
					"college,major,year,email,adminRight) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = con.CreatePreparedStatement(sql);
		try{	//写入信息
			ps.setString(1,AF.getAdminName());
			ps.setString(2,AF.getTrueName());
			ps.setString(3,AF.getPassword());
			ps.setString(4,AF.getQq());
			ps.setString(5,AF.getLongNum());
			ps.setString(6,AF.getShortNum());
			ps.setString(7,AF.getAddress());
			ps.setString(8,AF.getCollege());
			ps.setString(9,AF.getMajor());
			ps.setInt(10,AF.getYear());
			ps.setString(11,AF.getEmail());
			ps.setInt(12, AF.getAdminRight());
			row = ps.executeUpdate();
			if(row > 0)
				System.out.println("成功添加了" + row + "条数据");
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
			e.printStackTrace();
			}
		}
		con.closeConnection();
		return row;
	}
	
	public static void DBAdminLogin(LoginInformation LI){	//登陆数据库读取，验证账户信息
		JDBConnection con = new JDBConnection();
		String sql = "SELECT * FROM yx_admin WHERE adminName='" + LI.adminName + "'";
		ResultSet rs = con.executeQuery(sql);
		try{
			if(rs.next()){
				LI.id = rs.getInt("adminID");
				LI.password = rs.getString("password");
				LI.TrueName = rs.getString("TrueName");
				LI.adminright = rs.getInt("adminright");
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con.closeConnection();
	}
	
	
	
	
	
	public static int DocItemUpdate(int itemID ,int adminID){
		int flag = -1;
		JDBConnection con=new JDBConnection();
		String sql = "SELECT * FROM yx_docitem WHERE itemID='" + ((Integer)itemID).toString() + "'";
		ResultSet rs = con.executeQuery(sql);
		String sql2 = "update yx_docitem set adminID=? WHERE itemID=?";
		PreparedStatement ps = con.CreatePreparedStatement(sql2);
		try{	
			if(rs.getString("adminID") != null)
				flag = 0;
			else{
				ps.setInt(1, adminID);
				ps.setInt(2, itemID);
				ps.executeUpdate();
				flag = 1;
			}
		}catch(SQLException e){
			e.printStackTrace();
			flag = -1;
		}finally{
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
			e.printStackTrace();
			}
		}
		con.closeConnection();
		return flag;	//1为成功，0为已经被接单，-1操作不成功
	}
	
	public static List<DocItem> GetDocItem(int userID,int page){
		JDBConnection con = new JDBConnection();
		Integer pagestart = (page - 1) * 5;
		String sql = "select * from yx_docitem WHERE userID = "+ ((Integer)userID).toString() + " order by subTime desc limit " + pagestart.toString() + ", 5";
		ResultSet rs = con.executeQuery(sql);
		List <DocItem> L = new ArrayList<DocItem>();
		try{
			while(rs.next()){
				DocItem DI = new DocItem();
				DI.setitemID(rs.getInt("itemID"));
				DI.setitemType(rs.getInt("itemType"));
				DI.setprintType(rs.getInt("printType"));
				DI.setuserID(rs.getInt("userID"));
				DI.setadminID(rs.getInt("adminID"));
				DI.setstudentNum(rs.getString("studentNum"));
				DI.setTrueName(rs.getString("TrueName"));
				DI.setpaperType(rs.getString("paperType"));
				DI.setcopies(rs.getInt("copies"));
				DI.setisColor(rs.getInt("isColor"));
				DI.setisSingle(rs.getInt("isSingle"));
				DI.setppts(rs.getInt("ppts"));
				DI.setotherRequest(rs.getString("otherRequest"));
				DI.setitemStatus(rs.getInt("itemStatus"));
				DI.setdocFile(rs.getString("docFile"));
				DI.setSubTime(rs.getLong("SubTime")); 
				DI.setTakeTime(rs.getLong("takeTime"));
				DI.setmoney(rs.getFloat("money"));
				L.add(DI);
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con.closeConnection();
		return L;
	}
	
	
	public static int getCount(int userID) {
		JDBConnection con = new JDBConnection();
		String sql = "select count(*) from yx_docitem where userID = " + ((Integer)userID).toString();
		ResultSet rs = con.executeQuery(sql);
		int temp = 0;
		try {
			rs.next();
			temp = rs.getInt(1);
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		con.closeConnection();
		return temp;
	}
	
	public static List<DocItem> getAllDocItem(int page){
		JDBConnection con = new JDBConnection();
		Integer pagestart = (page - 1) * 5;
		String sql = "select * from yx_docitem order by subTime desc limit " + pagestart.toString() + ", 5";
		ResultSet rs = con.executeQuery(sql);
		List <DocItem> L = new ArrayList<DocItem>();
		try{
			while(rs.next()){
				DocItem DI = new DocItem();
				DI.setitemID(rs.getInt("itemID"));
				DI.setitemType(rs.getInt("itemType"));
				DI.setprintType(rs.getInt("printType"));
				DI.setuserID(rs.getInt("userID"));
				DI.setadminID(rs.getInt("adminID"));
				DI.setstudentNum(rs.getString("studentNum"));
				DI.setTrueName(rs.getString("TrueName"));
				DI.setpaperType(rs.getString("paperType"));
				DI.setcopies(rs.getInt("copies"));
				DI.setisColor(rs.getInt("isColor"));
				DI.setisSingle(rs.getInt("isSingle"));
				DI.setppts(rs.getInt("ppts"));
				DI.setotherRequest(rs.getString("otherRequest"));
				DI.setitemStatus(rs.getInt("itemStatus"));
				DI.setdocFile(rs.getString("docFile"));
				DI.setSubTime(rs.getLong("SubTime")); 
				DI.setTakeTime(rs.getLong("takeTime"));
				DI.setmoney(rs.getFloat("money"));
				L.add(DI);
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con.closeConnection();
		return L;
	}
	
	public static int getAllCount() {
		JDBConnection con = new JDBConnection();
		String sql = "select count(*) from yx_docitem ";
		ResultSet rs = con.executeQuery(sql);
		int temp = 0;
		try {
			rs.next();
			temp = rs.getInt(1);
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		con.closeConnection();
		return temp;
	}
}
